package com.chendongyang.www.dao;
import com.chendongyang.www.po.apply;
import com.mysql.cj.jdbc.Driver;

import java.sql.*;
import java.util.ArrayList;

public class appling {
    public boolean select(String userName) throws SQLException {
        //判断要注册时该注册名是否已存在；
        String i="0";
        DriverManager.registerDriver(new Driver());
        Connection root = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lle", "root", "123456");
        String sql="select id from dianche1 where id=?and 车牌号<>?;";
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        preparedStatement.setString(1,userName);
        preparedStatement.setString(2,i);
        ResultSet resultSet = preparedStatement.executeQuery();
        boolean result = false;
        while(resultSet.next()){
            result=true;
        }
        root.close();
        preparedStatement.close();
        resultSet.close();
        return result;
    }
    public String select1(String userName) throws SQLException {
        //判断要注册时该注册名是否已存在；
        String i="0";
        DriverManager.registerDriver(new Driver());
        Connection root = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lle", "root", "123456");
        String sql="select * from dianche1 where id=? and 车牌号<>?;";
        PreparedStatement preparedStatement = root.prepareStatement(sql);
       preparedStatement.setString(1,userName);
        preparedStatement.setString(2,i);
        ResultSet resultSet = preparedStatement.executeQuery();
       String result="0";
        while (resultSet.next()) {
           result=resultSet.getString("车牌号");
        }
        root.close();
        preparedStatement.close();
        resultSet.close();
        return result;
    }
    public  int insert(String user,String name) throws Exception{
        //进行数据的插入（用户注册）
        DriverManager.registerDriver(new Driver());
        Connection root = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lle", "root", "123456");
        String sql="insert into application values (?,?);";
        int line;
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        preparedStatement.setString(1, user);
        preparedStatement.setString(2, name);
        line = preparedStatement.executeUpdate();
        preparedStatement.close();
        root.close();
        return line;
    }
    public ArrayList<apply> select1() throws SQLException {
        ArrayList<apply> List=new ArrayList<>();
        DriverManager.registerDriver(new Driver());
        Connection root=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lle","root","123456");
        String sql="select * from  application;";
        Statement statement=root.createStatement();
        ResultSet resultSet=statement.executeQuery(sql);
        while(resultSet.next()){
            apply a=new apply();
            a.setId(resultSet.getString(1));
            a.setName(resultSet.getString(2));
            List.add(a);
        }
        root.close();
        statement.close();
        resultSet.close();
        return List;
    }
    public  int insert1(String lcnumber,String id) throws Exception{
        DriverManager.registerDriver(new Driver());
        Connection root = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lle", "root", "123456");
        String sql="update dianche1 set 车牌号=? where id=?;";
        int line;
        PreparedStatement preparedStatement = root.prepareStatement(sql);
        preparedStatement.setString(1, lcnumber);
        preparedStatement.setString(2, id);
        line = preparedStatement.executeUpdate();
        preparedStatement.close();
        root.close();
        return line;
    }
    public  void delete() throws SQLException {
            DriverManager.registerDriver(new Driver());
            Connection root = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/lle", "root", "123456");
            String sql = "delete from application;";
            PreparedStatement preparedStatement = root.prepareStatement(sql);
        int i = preparedStatement.executeUpdate(sql);
        preparedStatement.close();
            root.close();
    }
}
